-- =============================================
-- This stored procedure moves a content record to a specified folder/nav menu.
--
-- Modification
-- 09/08/2003    E.Tatsui   Created
-- =============================================

CREATE          PROCEDURE amsp_CMUpdateContentNavMenu
    @InMoveContentID numeric,
  @InTargetNavMenuID numeric,
  @InContactID numeric,
  @OutErrorMessage varchar(255) OUTPUT
AS
BEGIN
  DECLARE
    @OrigURLSafeName varchar(255),
    @URLSafeName varchar(255),
    @OriginalNavMenuID numeric,
    @DefaultContentID numeric,
    @ContentNum numeric,
    @NavContentGroupInd char(1),
    @RecycleBinID numeric,
    @WorkflowStatusCode char(1),
    @ContentID numeric,
    @OrigNavContentGroupInd char(1)

  SELECT @RecycleBinID = NavMenuID
    FROM Nav_Menu
   WHERE Name = 'Content_Recycle_Bin'

  SELECT @OrigURLSafeName = a.URLSafeName,
         @OriginalNavMenuID = a.NavMenuID,
         @DefaultContentID = b.ContentID,
         @ContentNum = (SELECT Count(*) FROM vCurrent_Content WHERE NavMenuID = b.NavMenuID),
         @OrigNavContentGroupInd = b.NavContentGroupInd,
         @WorkflowStatusCode = a.WorkflowStatusCode
    FROM Content a WITH (NOLOCK), Nav_Menu b WITH (NOLOCK)
   WHERE a.ContentID = @InMoveContentID
     AND a.NavMenuID = b.NavMenuID

  SELECT @NavContentGroupInd = NavContentGroupInd
    FROM Nav_Menu WITH (NOLOCK)
   WHERE NavMenuID = @InTargetNavMenuID

  -- If the target is recycle bin, delete the content.
  IF @InTargetNavMenuID = @RecycleBinID BEGIN  
    IF @WorkflowStatusCode IN ('P')
      EXEC amsp_CMDeleteContent @InMoveContentID, @InContactID, 'N', NULL
    ELSE BEGIN -- There is a working version. Mark it as recycled.
      UPDATE Content
         SET WorkflowStatusCode = 'Y',
             ContactID = @InContactID
       WHERE ContentID = @InMoveContentID

      INSERT INTO Content_Workflow_Log (
             ContentID,
             WorkflowStatusCode,
             ContactID,
             ChangeDateTime)
      VALUES (@InMoveContentID,
             'Y',
              @InContactID,
              CURRENT_TIMESTAMP)
    END
  END
  ELSE BEGIN -- Normal move.
    -- If there is no working version, create one.
    IF @WorkflowStatusCode NOT IN  ('W','A','E')
      EXEC amsp_CMGetWorkingContentID @InMoveContentID, @InContactID, @ContentID OUTPUT, 'N'
    ELSE
      SET @ContentID = @InMoveContentID

    -- Since ContentID has changed, get the values again.
    IF @ContentID <> @InMoveContentID
      SELECT @OrigURLSafeName = a.URLSafeName,
             @OriginalNavMenuID = a.NavMenuID,
             @DefaultContentID = b.ContentID,
             @ContentNum = (SELECT Count(*) FROM vCurrent_Content WHERE NavMenuID = b.NavMenuID),
             @OrigNavContentGroupInd = b.NavContentGroupInd,
             @WorkflowStatusCode = a.WorkflowStatusCode
        FROM Content a WITH (NOLOCK), Nav_Menu b WITH (NOLOCK)
       WHERE a.ContentID = @ContentID
         AND a.NavMenuID = b.NavMenuID

    EXEC amsp_CMGetUniqueContentName NULL, @OrigURLSafeName, @InTargetNavMenuID, @URLSafeName OUTPUT

    UPDATE Content
       SET NavMenuID = @InTargetNavMenuID,
           URLSafeName = @URLSafeName,
           SortOrder = (SELECT IsNull(Max(SortOrder),0)+1
                          FROM vCurrent_Content
                         WHERE NavMenuID = @InTargetNavMenuID),
           ContactID = @InContactID
     WHERE ContentID = @ContentID
  
    -- If destination is navigation menu.
    IF @NavContentGroupInd = 'N' BEGIN
      -- If this is the first content, make it default one.
      UPDATE Nav_Menu
         SET ContentID = @ContentID
       WHERE NavMenuID = @InTargetNavMenuID
         AND ContentID IS NULL
    END
    -- If content belonged to a nav item before.
    IF @OrigNavContentGroupInd = 'N' BEGIN
      -- If this is default content, need to assign a new default content.
      IF @DefaultContentID = @InMoveContentID BEGIN
        IF @ContentNum > 2
          UPDATE Nav_Menu
             SET ContentID = (SELECT TOP 1 ContentID
                                FROM vCurrent_Content
                               WHERE NavMenuID = @OriginalNavMenuID
                               ORDER BY SortOrder),
                 WorkflowStatusCode = 'W',
                 PublishedDateTime = NULL
           WHERE NavMenuID = @OriginalNavMenuID
        ELSE
          UPDATE Nav_Menu
             SET ContentID = NULL,
                 DirectListComboInd = 'D',
                 WorkflowStatusCode = 'W',
                 PublishedDateTime = NULL
           WHERE NavMenuID = @OriginalNavMenuID
      END
      -- Resort content.
      EXEC amsp_CMRenumCurrentContent @OriginalNavMenuID
    END

  END

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMUpdateContentNavMenu] TO [IMIS]
GO